package com.central.apps.controller;

import com.central.app.common.util.DateUtils;
import com.central.app.service.HandlerService;
import com.central.common.util.ToolUtil;
import com.central.common.web.Result;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.math.BigDecimal;
import java.util.*;

@RestController
@RequestMapping("/query")
public class QueryController {

    @Autowired private HandlerService handlerService;

    /**
     * 库存月结选择物料按钮
     * @return
     */
    @PostMapping("/getitemapplys")
    public Map<String,Object> getitemapplys(@RequestBody Map<String,Object> params) throws Exception {
        Map<String,Object> result =new HashMap<>();
        String id = MapUtils.getString(params,"id");
        String storenum = MapUtils.getString(params,"storenum");
        Integer year = MapUtils.getInteger(params, "year");
        Integer month = MapUtils.getInteger(params,"month");

        //该库房下该月只能月结一次
        List<Map<String, Object>> bySql3 = handlerService.findBySql("select * from gq_query where storenum = '" + storenum + "' and year = " + year + " and month =" + month + " and id not in (" + id + ")");
        if(CollectionUtils.isNotEmpty(bySql3)){
            result.put("code",1);
            result.put("msg",year+"年"+month+"月,"+"编号为"+storenum+"的库房已经生成了库存月结，请勿重复进行月结！");
            return result;
        }

        //获取该月第一天，最后一天
        String firstDayOfMonth1 = DateUtils.getFirstDayOfMonth1(year, month);
        String lastDayOfMonth1 = DateUtils.getLastDayOfMonth1(year, month);
        /**
         * 为了解决这个月出库了，导致余量为0了，月结查不到
         */
        //查询该库房下库存余量大于0的物料信息
        List<String> invbalancesBatch = new ArrayList<>();
        String sql ="select batchnum from gq_invbalances where storenum ='"+storenum+"' and avblbalance >0";
        List<Map<String, Object>> bySql = handlerService.findBySql(sql);
        bySql.stream().forEach(map -> {invbalancesBatch.add(MapUtils.getString(map,"batchnum")); });

        //查出这个月内该库房下出入库的物料信息
        List<String> matuseBatch = new ArrayList<>();
        String sqlMatuser = "select lotnum from gq_matusetrans where storenum = '" + storenum + "' and DATE_FORMAT(createtime,'%Y-%m-%d') BETWEEN '" + firstDayOfMonth1 + "' and '" + lastDayOfMonth1 + "'";
        List<Map<String, Object>> bySql8 = handlerService.findBySql(sqlMatuser);
        bySql8.stream().forEach(map -> {matuseBatch.add(MapUtils.getString(map,"lotnum"));});

        //取并集，根据并集后的批次查库存余量
        List<String> totalList = new ArrayList<>(CollectionUtils.union(invbalancesBatch, matuseBatch));
        StringBuilder stringBuilder = new StringBuilder();
        StringJoiner stringJoiner = new StringJoiner("','");
        if(CollectionUtils.isNotEmpty(totalList)){
            for (String s : totalList) {
                stringJoiner.add(s);
            }
        }
        StringBuilder append = stringBuilder.append("'").append(stringJoiner.toString()).append("'");
        List<Map<String, Object>> bySql7 = handlerService.findBySql("select a.subjectno,a.proid,a.taskid,a.itemnum,a.itemdesc,a.curbal,a.binnum,a.batchnum,a.unitcost from gq_invbalances a left join gq_inventory b on a.storenum=b.storenum and a.itemnum=b.itemnum where a.batchnum in (" + append.toString() + ") and b.storenum = '" + storenum +"'");

        for(Map<String, Object> map :bySql7){
            String itemnum =MapUtils.getString(map,"itemnum");
            String batchnum =MapUtils.getString(map,"batchnum");
            String avgcost =MapUtils.getString(map,"unitcost");
            String stdcost =MapUtils.getString(map,"unitcost");
            String binnum =MapUtils.getString(map,"binnum");

            //该库房+物料+批次的期初数量
            String endnum = "0";//该物料的期初数量
            String endmoney = "0";//该物料的期初金额
            String sql2 = "select endnum,endmoney from gq_queryline where binnum = '" + binnum + "' and itemnum = '" + itemnum + "' and lotnum = '" + batchnum +"' order by createtime desc limit 1";
            List<Map<String, Object>> bySql9 = handlerService.findBySql(sql2);
            if(CollectionUtils.isNotEmpty(bySql9)){
                endnum = MapUtils.getString(bySql9.get(0),"endnum");
                endmoney = MapUtils.getString(bySql9.get(0),"endmoney");
            }
            map.put("beginnum",new BigDecimal(endnum==null?"0":endnum).toString());
            map.put("beginmoney",new BigDecimal(endmoney==null?"0":endmoney).toString());

            //查询成本表价格
            Float money =(float)0;
            List<Map<String, Object>> bySql4 = handlerService.findBySql("select avgcost from gq_invcost where itemnum ='" + itemnum + "' and storenum ='" + storenum + "' and batchnum ='"+batchnum+"'");
            if(ToolUtil.isNotEmpty(bySql4)) {
                money = MapUtils.getFloat(bySql4.get(0), "avgcost");
            }
            //本月入库数量，本月入库金额
            String binnumSql = "";
            if(StringUtils.isNotBlank(binnum)){
                binnumSql = " and binnum='"+binnum+"'";
            }
            List<Map<String, Object>> bySql1 = handlerService.findBySql("select sum(quantity) as quantity  from gq_matusetrans where type in('接收','退回','调入','盘盈') and itemnum ='" + itemnum + "' and storenum ='" + storenum + "' and lotnum ='"+batchnum+"' and DATE_FORMAT(createtime,'%Y-%m-%d') BETWEEN '" + firstDayOfMonth1 + "' and '" + lastDayOfMonth1 + "'"+binnumSql);
            if(ToolUtil.isNotEmpty(bySql1)) {
                if(MapUtils.getFloat(bySql1.get(0), "quantity")==null){
                    map.put("innum", 0);
                    map.put("inmoney", 0);
                }else{
                    BigDecimal bigDecimal = new BigDecimal(money);
                    String quantity1 = MapUtils.getString(bySql1.get(0), "quantity");
                    BigDecimal quantity = new BigDecimal(quantity1);
                    map.put("innum", MapUtils.getFloat(bySql1.get(0), "quantity"));
                    map.put("inmoney", quantity.multiply(bigDecimal));
                }
            }else{
                map.put("innum", 0);
                map.put("inmoney", 0);
            }

            //本月出库数量，本月出库金额
            List<Map<String, Object>> bySql2 = handlerService.findBySql("select sum( quantity ) as quantity from gq_matusetrans where type in('领用出库','调出','报废','盘亏','报废出库','销售出库','返厂维修') and itemnum ='" + itemnum + "' and storenum ='" + storenum + "' and lotnum ='"+batchnum+"' and DATE_FORMAT(createtime,'%Y-%m-%d') BETWEEN '" + firstDayOfMonth1 + "' and '" + lastDayOfMonth1 + "'" + binnumSql);
            if(ToolUtil.isNotEmpty(bySql2)) {
                if(MapUtils.getFloat(bySql2.get(0), "quantity")==null){
                    map.put("outnum", 0);
                    map.put("outmoney", 0);
                }else{
                    BigDecimal bigDecimal = new BigDecimal(money);
                    String quantity1 = MapUtils.getString(bySql2.get(0), "quantity");
                    BigDecimal quantity = new BigDecimal(quantity1);
                    map.put("outnum", quantity);
                    map.put("outmoney", quantity.multiply(bigDecimal));
                }
            }else{
                map.put("outnum", 0);
                map.put("outmoney", 0);
            }

            //本月结存数量，结存金额
            map.put("endnum",new BigDecimal(MapUtils.getString(map,"beginnum")).add(new BigDecimal(MapUtils.getString(map,"innum"))).subtract(new BigDecimal(MapUtils.getString(map,"outnum"))).toString());
            map.put("endmoney",new BigDecimal(MapUtils.getString(map,"beginmoney")).add(new BigDecimal(MapUtils.getString(map,"inmoney"))).subtract(new BigDecimal(MapUtils.getString(map,"outmoney"))).toString());
            map.remove("batchnum");
            map.put("lotnum",batchnum);

            map.remove("unitcost");
            map.put("avgcost",avgcost);
            map.put("stdcost",stdcost);
            map.put("queryid",id);
            map.put("location",storenum);//仓库

            List<Map<String, Object>> bySql6 = handlerService.findBySql("select model,unit from gq_item where itemnum ='" + itemnum + "'");
            if(ToolUtil.isNotEmpty(bySql6)){
                map.put("unit",MapUtils.getString(bySql6.get(0),"unit"));
                map.put("model",MapUtils.getString(bySql6.get(0),"model"));
            }

            //课题号，任务
            List<Map<String, Object>> pronameList = handlerService.findBySql("select proname from gq_proaccount where id = '" + MapUtils.getString(map, "proid") + "'");
            if(CollectionUtils.isNotEmpty(pronameList)){
                String proname = MapUtils.getString(pronameList.get(0), "proname");
                map.put("proname",proname);
            }
            handlerService.insert("gq_queryline",map);
        }

        //变更状态，提取库存按钮禁用
        handlerService.updateBySql("update gq_query set status ='succeed' where id ='"+id+"'");

        //汇总月结主表数量合计，金额合计
        List<Map<String, Object>> bySql1 = handlerService.findBySql("select endnum,endmoney from gq_queryline where queryid ='" + id + "'");
        Float totalnumber=(float)0;
        BigDecimal totalcost = new BigDecimal("0");
        for(Map<String, Object> map :bySql1){
            Float endnum =MapUtils.getFloat(map,"endnum");
            String endmoney1 =MapUtils.getString(map,"endmoney");
            BigDecimal endmoney = new BigDecimal(endmoney1);
            if(endnum ==null){endnum=(float)0;}
            totalnumber =totalnumber +endnum;
            if(endmoney ==null){}
            totalcost =totalcost.add(endmoney);
        }
        handlerService.updateBySql("update gq_query set totalnumber ="+totalnumber+",totalcost ="+totalcost+" where id ='"+id+"'");
        result.put("code",0);
        result.put("msg","插入"+bySql7.size()+"条子表数据!");
        return result;
    }

    //根据传的库房   根据该库房的上次月结月份决定这次月结的月份
    @GetMapping("/inventoryMonthly")
    public Result inventoryMonthly(@RequestParam Map<String, Object> params) throws Exception {
        String storenum = MapUtils.getString(params, "storenum");//库房
        HashMap<String, Object> map = new HashMap<>();
        //1.根据所选库房（该库房上一次月结的年月）来决定这次月结的年月是多少。
        try {
            String sql = "select * from gq_query where storenum = '" + storenum + "' order by createtime desc limit 1";
            List<Map<String, Object>> bySql = handlerService.findBySql(sql);
            if(CollectionUtils.isNotEmpty(bySql)){
                Integer year = MapUtils.getInteger(bySql.get(0), "year");
                Integer month = MapUtils.getInteger(bySql.get(0), "month");
                if(12 == month){
                    year = year + 1;
                    month = 1;
                }else{
                    month = month + 1;
                }
                //月结不能超过本月
                int currentMonth = DateUtils.getCurrentMonth();
                if(month > currentMonth){
                    month = currentMonth;
                }
                map.put("year",year);
                map.put("month",month);
            }else{
                //库房没有进行过月结，就按照最初默认的月结年月，这个等飞哥待定，先给个2023年5月
                map.put("year",2023);
                map.put("month",5);
            }

            return  Result.succeed(map,"查询成功");
        }catch (Exception e){
            e.printStackTrace();
        }
        return  Result.succeed(map,"查询成功");
    }
}
